package com.whz.workmanageol.utils;

import com.whz.workmanageol.dao.*;
import com.whz.workmanageol.entity.*;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Component;
import org.springframework.web.multipart.MultipartFile;

import java.io.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

/**
 * 根据导入的Excel文件 将信息保存到数据库
 */
@Component
public class ReadExcel {

    @Autowired
    private TeacherMapper teacherMapper;
    @Autowired
    private StudentMapper studentMapper;
    @Autowired
    private GradeMapper gradeMapper;
    @Autowired
    private CourseMapper courseMapper;
    /**
     * 读取学生Excel表格中的信息
     * @param file
     * @return
     */
    public List<Student> readStudent(File file){
        if(!file.getName().endsWith("studentinfo.xlsx")) return null;
        List<Student> studentList = new ArrayList<>();
        List<Map<String, String>> mapList = readExcel(file);
        for(Map<String, String> map : mapList){
            Student student = new Student();
            for (Map.Entry<String, String> entry : map.entrySet()) {
                if("学号".equals(entry.getKey())){
                    student.setStudentid(entry.getValue());
                }
                if("密码".equals(entry.getKey())){
                    student.setPassword(entry.getValue());
                }
                if("姓名".equals(entry.getKey())){
                    student.setUsername(entry.getValue());
                }
                if("电子邮箱".equals(entry.getKey())){
                    student.setEmail(entry.getValue());
                }
                if("电话号码".equals(entry.getKey())){
                    student.setTel(entry.getValue());
                }
            }
            ;
            studentList.add(student);
        }
        return studentList;
    }

    /**
     * 读取教师Excel表格中的信息
     * @param file
     * @return
     */
    public List<Teacher> readTeacher(File file){
        if(!file.getName().endsWith("teacherinfo.xlsx")) return null;
        List<Teacher> teacherList = new ArrayList<>();
        List<Map<String, String>> mapList = readExcel(file);
        for(Map<String, String> map : mapList){
            Teacher teacher = new Teacher();
            for (Map.Entry<String, String> entry : map.entrySet()) {
                if("工号".equals(entry.getKey())){
                    teacher.setTeacherid(entry.getValue());
                }
                if("密码".equals(entry.getKey())){
                    teacher.setPassword(entry.getValue());
                }
                if("姓名".equals(entry.getKey())){
                    teacher.setUsername(entry.getValue());
                }
                if("电子邮箱".equals(entry.getKey())){
                    teacher.setEmail(entry.getValue());
                }
                if("电话号码".equals(entry.getKey())){
                    teacher.setTel(entry.getValue());
                }
            }
            ;
            teacherList.add(teacher);
        }
        return teacherList;
    }

    public List<Grade> readGrade(File file){
        if(!file.getName().endsWith("gradeinfo.xlsx")) return null;
        List<Grade> gradeList = new ArrayList<>();
        List<Map<String, String>> mapList = readExcel(file);
        for(Map<String, String> map : mapList){
            Grade grade = new Grade();
            for (Map.Entry<String, String> entry : map.entrySet()) {
                if("班级编号".equals(entry.getKey())){
                    grade.setGradeid(entry.getValue());
                }
                if("班级名称".equals(entry.getKey())){
                    grade.setGradename(entry.getValue());
                }
            }
            ;
            gradeList.add(grade);
        }
        return gradeList;
    }

    public List<Course> readCourse(File file){
        if(!file.getName().endsWith("courseinfo.xlsx")) return null;
        List<Course> courseList = new ArrayList<>();
        List<Map<String, String>> mapList = readExcel(file);
        for(Map<String, String> map : mapList){
            Course course = new Course();
            for (Map.Entry<String, String> entry : map.entrySet()) {
                if("课程编号".equals(entry.getKey())){
                    course.setCourseid(entry.getValue());
                }
                if("课程名称".equals(entry.getKey())){
                    course.setCoursename(entry.getValue());
                }
            }
            ;
            courseList.add(course);
        }
        return courseList;
    }

    public List<GradeCourse> readGradeCourse(File file){
        if(!file.getName().endsWith("gradecourseinfo.xlsx")) return null;
        List<GradeCourse> gradeCourseList = new ArrayList<>();
        List<Map<String, String>> mapList = readExcel(file);
        for(Map<String, String> map : mapList){
            GradeCourse gradeCourse = new GradeCourse();
            for (Map.Entry<String, String> entry : map.entrySet()) {
                if("班级名称".equals(entry.getKey())){
                    String gradeName = entry.getValue();
                    // 根据班级名称取出班级表ID
                    String gradeid = gradeMapper.findIDByGradename(gradeName);
//                    String gradeid = gradeRepository.findIDByGradename(gradeName);
                    gradeCourse.setGradeid(gradeid);
                }
                if("课程名称".equals(entry.getKey())){
                    String courseName = entry.getValue();
                    // 根据课程名称取出课程表ID
                    String courseid = courseMapper.findIDByCoursename(courseName);
//                    String courseid = courseRepository.findIDByCoursename(courseName);
                    gradeCourse.setCourseid(courseid);
                }
            }
            ;
            gradeCourseList.add(gradeCourse);
        }
        return gradeCourseList;
    }

    /**
     * 学生与班级是一对多的关系 在学生表中直接存储班级ID
     */
    public void readStuGrade(File file){
        List<Map<String, String>> mapList = readExcel(file);
        for(Map<String, String> map : mapList){
            String studentid = null;
            String gradeName = null;
            for (Map.Entry<String, String> entry : map.entrySet()) {
                if("学生学号".equals(entry.getKey())){
                    studentid = entry.getValue();
                }
                if("班级名称".equals(entry.getKey())){
                    gradeName = entry.getValue();
                }
            };
            studentMapper.saveGradeid(gradeName, studentid);
        }
    }

    public List<StudentCourse> readStuCourse(File file){
        if(!file.getName().endsWith("studentcourseinfo.xlsx")) return null;
        List<StudentCourse> studentCourseList = new ArrayList<>();
        List<Map<String, String>> mapList = readExcel(file);
        for(Map<String, String> map : mapList){
            StudentCourse studentCourse = new StudentCourse();
            for (Map.Entry<String, String> entry : map.entrySet()) {
                if("学生学号".equals(entry.getKey())){
                    studentCourse.setStudentid(studentMapper.findIDByStudentid(entry.getValue()));
                }
                if("课程名称".equals(entry.getKey())){
                    studentCourse.setCourseid(courseMapper.findIDByCoursename(entry.getValue()));
                }
            };
            studentCourseList.add(studentCourse);
        }
        return studentCourseList;
    }

    public List<TeacherGrade> readTeaGrade(File file){
        if(!file.getName().endsWith("teachergradeinfo.xlsx")) return null;
        List<TeacherGrade> teacherGradeList = new ArrayList<>();
        List<Map<String, String>> mapList = readExcel(file);
        for(Map<String, String> map : mapList){
            TeacherGrade teacherGrade = new TeacherGrade();
            for (Map.Entry<String, String> entry : map.entrySet()) {
                if("教师工号".equals(entry.getKey())){
                    teacherGrade.setTeacherid(teacherMapper.findIDByTeacherid(entry.getValue()));
                }
                if("班级名称".equals(entry.getKey())){
                    teacherGrade.setGradeid(gradeMapper.findIDByGradename(entry.getValue()));
                }
            };
            teacherGradeList.add(teacherGrade);
        }
        return teacherGradeList;
    }

    public List<TeacherCourse> readTeaCourse(File file){
        if(!file.getName().endsWith("teachercourseinfo.xlsx")) return null;
        List<TeacherCourse> teacherCourseList = new ArrayList<>();
        List<Map<String, String>> mapList = readExcel(file);
        for(Map<String, String> map : mapList){
            TeacherCourse teacherCourse = new TeacherCourse();
            for (Map.Entry<String, String> entry : map.entrySet()) {
                if("教师工号".equals(entry.getKey())){
                    teacherCourse.setTeacherid(teacherMapper.findIDByTeacherid(entry.getValue()));
                }
                if("课程名称".equals(entry.getKey())){
                    teacherCourse.setCourseid(courseMapper.findIDByCoursename(entry.getValue()));
                }
            };
            teacherCourseList.add(teacherCourse);
        }
        return teacherCourseList;
    }

    private List<Map<String, String>> readExcel(File file){
        List<Map<String, String>> mapList = new ArrayList<>();
        // 判断文件是否存在
        if(file.isFile() && file.exists()){
            if(file.getName().endsWith(".xlsx")){
                Workbook workbook;
                // 文件为Excel文件
                try {
                    FileInputStream inputStream = new FileInputStream(file);
                    workbook = new XSSFWorkbook(inputStream);
                    // 开始解析Excel文件
                    Sheet sheet = workbook.getSheetAt(0);
                    // 第一行是列名 从第二行遍历
                    int firstRowNum = sheet.getFirstRowNum() + 1;
                    int lastRowNum = sheet.getLastRowNum();
                    // 遍历行
                    for(int rIndex = firstRowNum; rIndex <= lastRowNum; rIndex++){
                        Map<String, String> map = new HashMap<>();
                        Row row = sheet.getRow(rIndex);
                        if(row != null){
                            int firstCellNum = row.getFirstCellNum();
                            int lastCellNum = row.getLastCellNum();
                            for(int cIndex = firstCellNum; cIndex < lastCellNum; cIndex++){
                                row.getCell(cIndex).setCellType(CellType.STRING);
                                // 获取单元格的值
                                String value = row.getCell(cIndex).getStringCellValue();
                                // 获取此单元格对应第一行的值
                                String key = sheet.getRow(0).getCell(cIndex).getStringCellValue();
                                map.put(key, value);
                            }
                        }
                        mapList.add(map);
                    }
                } catch (FileNotFoundException e) {
                    e.printStackTrace();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return mapList;
    }

}
